﻿if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SalesByYear]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SalesByYear]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE sp_SalesByYear
   @yearNum int = 1997
AS
BEGIN
	SET NOCOUNT ON;
SELECT 
  [Order].ShippedDate, 
  [OrderSubtotals].OrderID, 
  [OrderSubtotals].Subtotal,
  'QuarterNum' = CASE 
  when MONTH(ShippedDate) between 1 and 3 then 1
  when MONTH(ShippedDate) between 4 and 6 then 2
  when MONTH(ShippedDate) between 7 and 9 then 3
  when MONTH(ShippedDate) between 10 and 12 then 4
  END,    
  Year([ShippedDate]) AS [Year]
FROM 
   [Order],
   (select * from udf_OrderSubtotals()) as [OrderSubtotals] 
    where 
     [Order].ID = [OrderSubtotals].OrderID and
     (Year([Order].ShippedDate) = @yearNum)
END
GO